﻿using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data.SqlClient;
using System.Data;
namespace DAO
{
    public class BANDAO
    {
        ///CỦA AI THÌ TỰ ĐÓNG KHUNG VÀO. TRÁNH XÓA NHẦM CODE
        ////////////CỦA LINH/////////////
        public List<BANDTO> LayDSBan(TANGDTO TDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();

            SqlCommand command = new SqlCommand("LINH07_LAYDSBANTRONG", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.Add("@MaTang", System.Data.SqlDbType.Int);
            command.Parameters["@MaTang"].Value = TDTO.MaTang;

            SqlDataReader reader = command.ExecuteReader();
            List<BANDTO> ds = new List<BANDTO>();
            while (reader.Read())
            {
                BANDTO BDTO = new BANDTO();
                BDTO.MaBan = reader.GetInt32(0);
                BDTO.TenBan = reader.GetString(1);
                BDTO.SucChua = reader.GetInt32(2);
                BDTO.SoKhach = 0;
                ds.Add(BDTO);
            }
            //Lấy danh sách bàn có khách LAYDSBANCOKHACH
            command = new SqlCommand("LINH09_LAYDSBANCOKHACH", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.Add("@MaTang", System.Data.SqlDbType.Int);
            command.Parameters["@MaTang"].Value = TDTO.MaTang;
            reader.Close();
            reader = command.ExecuteReader();
            while (reader.Read())
            {
                BANDTO BDTO = new BANDTO();
                BDTO.MaBan = reader.GetInt32(0);
                BDTO.TenBan = reader.GetString(1);
                BDTO.SucChua = reader.GetInt32(2);
                BDTO.SoKhach = reader.GetInt32(3);
                ds.Add(BDTO);
            }
            conn.Close();
            return ds;
        }
        public List<BANDTO> LayDSBan2(TANGDTO TDTO, int n)
        {
            SqlConnection conn = DataProvider.ConnectDB();

            SqlCommand command = new SqlCommand("LINH08_LAYDSBANTRONG2", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.Add("@MaTang", System.Data.SqlDbType.Int);
            command.Parameters["@MaTang"].Value = TDTO.MaTang;
            command.Parameters.Add("@SoKhach", System.Data.SqlDbType.Int);
            command.Parameters["@SoKhach"].Value = n;

            SqlDataReader reader = command.ExecuteReader();
            List<BANDTO> ds = new List<BANDTO>();
            while (reader.Read())
            {
                BANDTO BDTO = new BANDTO();
                BDTO.MaBan = reader.GetInt32(0);
                BDTO.TenBan = reader.GetString(1);
                BDTO.SucChua = reader.GetInt32(2);
                BDTO.SoKhach = 0;
                ds.Add(BDTO);
            }
            //Lấy danh sách bàn có khách LAYDSBANCOKHACH
            command = new SqlCommand("LINH10_LAYDSBANCOKHACH2", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.Add("@MaTang", System.Data.SqlDbType.Int);
            command.Parameters["@MaTang"].Value = TDTO.MaTang;
            command.Parameters.Add("@SoKhach", System.Data.SqlDbType.Int);
            command.Parameters["@SoKhach"].Value = n;
            reader.Close();
            reader = command.ExecuteReader();
            while (reader.Read())
            {
                BANDTO BDTO = new BANDTO();
                BDTO.MaBan = reader.GetInt32(0);
                BDTO.TenBan = reader.GetString(1);
                BDTO.SucChua = reader.GetInt32(2);
                BDTO.SoKhach = reader.GetInt32(3);
                ds.Add(BDTO);
            }
            conn.Close();
            return ds;
        }
        ///////////////////////////////////
        public DataTable TimBan(BANDTO BDTO)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand command = new SqlCommand("T10_TIMBAN", conn);
            command.Parameters.Add("@TenBan", System.Data.SqlDbType.Text).Value = BDTO.TenBan;
            command.Parameters.Add("@SucChua", System.Data.SqlDbType.Int).Value = BDTO.SucChua;
            command.CommandType = System.Data.CommandType.StoredProcedure;

            SqlDataAdapter da = new SqlDataAdapter(command);
            da.Fill(dt);

            conn.Close();
            return dt;
        }


        public int ThemBan(BANDTO BDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand("T11_THEMBAN", conn);
            cmd.Parameters.Add("@TenBan", System.Data.SqlDbType.NVarChar,50).Value = BDTO.TenBan;
            cmd.Parameters.Add("@SucChua", System.Data.SqlDbType.Int).Value = BDTO.SucChua;
            cmd.Parameters.Add("@MaTang", System.Data.SqlDbType.Int).Value = BDTO.MaTang;

            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq > 0)
            {
                return 1;
            }
            else { return 0; }
        }
        public int SuaBan(BANDTO BDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand("T12_SUABAN", conn);
            cmd.Parameters.Add("@TenBanCu", System.Data.SqlDbType.Text).Value = BDTO.TenBanCu;
            cmd.Parameters.Add("@TenBan", System.Data.SqlDbType.Text).Value = BDTO.TenBan;
            cmd.Parameters.Add("@SucChua", System.Data.SqlDbType.Int).Value = BDTO.SucChua;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            int kq = cmd.ExecuteNonQuery();
            cmd.ExecuteScalar();
            conn.Close();
            if (kq > 0)
                return 1;
            else
                return 0;
        }
        public int XoaBan(BANDTO BDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand("T13_XOABAN", conn);
            cmd.Parameters.Add("@MaBan", System.Data.SqlDbType.Int).Value = BDTO.MaBan;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq > 0)
                return 1;
            else
                return 0;
        }
        public DataTable ChiTietHD(BANDTO BDTO)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand command = new SqlCommand("T14_LAYCHITIETHD", conn);
            command.Parameters.Add("@MaBan", System.Data.SqlDbType.Int).Value = BDTO.MaBan;
            command.CommandType = System.Data.CommandType.StoredProcedure;

            SqlDataAdapter da = new SqlDataAdapter(command);
            da.Fill(dt);

            conn.Close();
            return dt;
        }
        public DataTable TimBanFull(BANDTO BDTO)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand command = new SqlCommand("T9_TIMBANFULL", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            SqlDataAdapter da = new SqlDataAdapter(command);
            da.Fill(dt);

            conn.Close();
            return dt;
        }
        public DataTable QLBanXoa(BANDTO BDTO)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand command = new SqlCommand("T16_QLXOABAN", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            SqlDataAdapter da = new SqlDataAdapter(command);
            da.Fill(dt);

            conn.Close();
            return dt;
        }
        public int PhucHoiBan(BANDTO BDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand("T18_PHUCHOIBAN", conn);
            cmd.Parameters.Add("@MaBan", System.Data.SqlDbType.Int).Value = BDTO.MaBan;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq > 0)
                return 1;
            else
                return 0;
        }
        public DataTable LOADBAN(BANDTO BDTO)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand command = new SqlCommand("T22_LOADSUKIENBAN", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            SqlDataAdapter da = new SqlDataAdapter(command);
            da.Fill(dt);

            conn.Close();
            return dt;
        }
        public int XoaMatBan(BANDTO BDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand("T20_XOAMATBAN", conn);
            cmd.Parameters.Add("@MaBan", System.Data.SqlDbType.Int).Value = BDTO.MaBan;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq > 0)
                return 1;
            else
                return 0;
        }
        public DataTable DSBANXOA(BANDTO BDTO)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand command = new SqlCommand("T19_LAYDSBANXOA", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            SqlDataAdapter da = new SqlDataAdapter(command);
            da.Fill(dt);

            conn.Close();
            return dt;
        }
       
    }
}
